﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace CnGine.CodeGenerator.Help
{
    public class SQLHelper
    {

        private string _connection = "";
        private string ConnectionStr
        {
            set { this._connection = value; }
            get { return this._connection; }
        }


        public SQLHelper()
        {
        }
        /// <summary>
        /// 设置链接字符串
        /// </summary>
        /// <param name="connection">数据库链接字符串</param>
        public SQLHelper(string connection)
        {
            this.ConnectionStr = connection;
        }

        #region 【数据库操作】
        /// <summary>
        /// 测试数据库是否可以链接
        /// </summary>
        /// <param name="connectionStr"></param>
        /// <returns></returns>
        public static bool TestConnection(string connectionStr)
        {
            using (SqlConnection con = new SqlConnection(connectionStr))
            {
                try
                {
                    con.Open();
                    return true;
                }
                catch
                {
                    return false;
                }
            }
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="Sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataTable Query(string Sql, SqlParameter[] parameters)
        {
            using (SqlConnection con = new SqlConnection(_connection))
            {
                con.Open();
                DataSet ds = new DataSet();
                SqlCommand cmd = new SqlCommand(Sql, con);
                if (parameters != null)
                {
                    for (int i = 0; i < parameters.Length; i++)
                    {
                        cmd.Parameters.Add(parameters[i]);
                    }
                }
                SqlDataAdapter command = new SqlDataAdapter(cmd);
                command.Fill(ds, "ds");
                if (ds != null && ds.Tables.Count > 0) return ds.Tables[0];
                return null;
            }
        }
        #endregion

        #region 【获取数据库信息】
        public List<Models.DataBase> GetDataBase(string connection)
        {
            this.ConnectionStr = connection;
            List<Models.DataBase> tables = new List<Models.DataBase>();
            //查询表和视图的信息
            StringBuilder sbSQL = new StringBuilder();
            sbSQL.Append("SELECT name from Master..SysDatabases ORDER BY Name");

            DataTable table = Query(sbSQL.ToString(), null);
            if (table != null && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    DataRow r = table.Rows[i];
                    Models.DataBase t = new Models.DataBase();

                    t.Name = ParseStr(r["name"]);
                    //添加到表集合当中
                    tables.Add(t);
                }
            }
            return tables;
        }
        #endregion

        #region 【获取表和列的信息】
        /// <summary>
        /// 获取所有的数据表和视图的列信息
        /// </summary>
        /// <param name="connection"></param>
        /// <returns></returns>
        public List<Models.Table> GetTableAndColumn(string connection)
        {
            List<Models.Table> tables = this.GetTable(connection);
            List<Models.Column> columns = this.GetColumns(connection);
            foreach (Models.Table item in tables)
            {
                //循环给表的列加上值
                List<Models.Column> cs = columns.FindAll(delegate(Models.Column cc) { return cc.TableID == item.ID; });
                item.Columns = cs;
            }

            return tables;
        }
        /// <summary>
        /// 获取表以及视图的信息
        /// </summary>
        /// <param name="connection"></param>
        /// <returns></returns>
        public List<Models.Table> GetTable(string connection)
        {
            this.ConnectionStr = connection;
            List<Models.Table> tables = new List<Models.Table>();
            //查询表和视图的信息
            StringBuilder sbSQL = new StringBuilder();
            sbSQL.Append("select o.id,o.name,p.value as [description],");
            sbSQL.Append("      isview = case when xtype='v' then '1' else '0' end ");
            sbSQL.Append("from SysObjects o ");
            sbSQL.Append("left join sys.extended_properties p on p.major_id = o.id and p.minor_id=0 ");
            sbSQL.Append("where xtype='u' or xtype='v' order by isview,o.name");
            DataTable table = Query(sbSQL.ToString(), null);
            if (table != null && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    DataRow r = table.Rows[i];
                    Models.Table t = new Models.Table();

                    t.ID = ParseInt(r["id"]);
                    t.Name = ParseStr(r["name"]);
                    t.TableName = t.Name;
                    t.Description = ParseStr(r["description"]);
                    t.IsView = ParseBool(r["isview"]);
                    //添加到表集合当中
                    tables.Add(t);
                }
            }
            return tables;
        }
        /// <summary>
        /// 获取表和视图的列信息
        /// </summary>
        /// <param name="connection">数据库链接</param>
        /// <returns>列集合</returns>
        public List<Models.Column> GetColumns(string connection)
        {
            return this.GetColumns(connection, "");
        }
        /// <summary>
        /// 获取表和视图的列信息
        /// </summary>
        /// <param name="connection">数据库链接</param>
        /// <param name="tablename">要查询的表名或视图名</param>
        /// <returns>列集合</returns>
        public List<Models.Column> GetColumns(string connection, string tablename)
        {
            this.ConnectionStr = connection;
            List<Models.Column> tables = new List<Models.Column>();
            //查询表和视图的列信息
            StringBuilder sbSQL = new StringBuilder();
            sbSQL.Append("SELECT  ");
            sbSQL.Append("tableid		=	a.id,");
            sbSQL.Append("tablename		=   d.name,");
            sbSQL.Append("tableview		=	case when d.xtype='v' then '1' else '0' end,");
            sbSQL.Append("columnid		=   a.colorder,");
            sbSQL.Append("name			=   a.name,");
            sbSQL.Append("[type]		=   b.name,");
            sbSQL.Append("[length]		=   a.length,");
            sbSQL.Append("[xiaoshu]		=   isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),");
            sbSQL.Append("isidentity	=   case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then '1' else '0' end,");
            sbSQL.Append("isprimarykey	=   case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (");
            sbSQL.Append("          SELECT name FROM sysindexes WHERE indid in(  ");
            sbSQL.Append("          SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '1' else '0' end, ");
            sbSQL.Append("[isnull]		=   case   when   a.isnullable=1   then   '1' else   '0'   end, ");
            sbSQL.Append("[default]		=   isnull(e.text,''),");
            sbSQL.Append("[description]	=   isnull(g.[value],'') ");
            sbSQL.Append("FROM syscolumns a ");
            sbSQL.Append("left join systypes b on a.xusertype=b.xusertype ");
            sbSQL.Append(" inner join sysobjects d on a.id=d.id and (d.xtype='u' or d.xtype ='v') and d.name <> 'dtproperties' ");
            sbSQL.Append("left join  syscomments e on  a.cdefault=e.id ");
            sbSQL.Append("left join  sys.extended_properties g  on  a.id=g.major_id   and   a.colid=g.minor_id ");
            sbSQL.Append("left join sys.extended_properties f on d.id=f.major_id   and   f.minor_id=0 ");
            //如果传表名过来则查询具体表或视图的列结构，否则查询所有表和视图的列结构
            if (!string.IsNullOrEmpty(tablename))
                sbSQL.AppendFormat("where d.name='{0}' ", tablename);
            sbSQL.Append("ORDER BY tableview,d.name,a.colorder");

            DataTable table = Query(sbSQL.ToString(), null);
            if (table != null && table.Rows.Count > 0)
            {
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    DataRow r = table.Rows[i];
                    Models.Column c = new Models.Column();

                    c.TableID = ParseInt(r["tableid"]);
                    c.TableName = ParseStr(r["tablename"]);
                    c.TableView = ParseBool(r["tableview"]);
                    c.ColumnID = ParseInt(r["columnid"]);
                    c.Name = ParseStr(r["name"]);
                    c.Type = ParseStr(r["type"]);
                    c.Length = ParseInt(r["length"]);
                    c.Xiaoshu = ParseInt(r["xiaoshu"]);
                    c.IsIdentity = ParseBool(r["isidentity"]);
                    c.IsPrimaryKey = ParseBool(r["isprimarykey"]);
                    c.IsNull = ParseBool(r["isnull"]);
                    c.Default = ParseStr(r["default"]);
                    c.Description = ParseStr(r["description"]);
                    tables.Add(c);
                }
            }
            return tables;
        }
        #endregion

        #region  【类型转换】
        private string ParseStr(object o)
        {
            if (o != null) return o.ToString().Trim();
            else return "";
        }
        private int ParseInt(object o)
        {
            int value = 0;
            if (o != null) int.TryParse(o.ToString().Trim(), out value);
            return value;
        }
        private bool ParseBool(object o)
        {
            bool value = false;
            string v = "";
            if (o != null)
            {
                v = o.ToString().Trim();
                if (v == "1")
                {
                    value = true;
                }
                else if (v == "0")
                {
                    value = false;
                }
                else
                {
                    bool.TryParse(o.ToString().Trim(), out value);
                }
            }
            return value;
        }
        #endregion

    }
}
